package com.szholly.pro.watergas_server.ps.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.szholly.plug.safe.entity.user.UserEntity;
import org.springframework.stereotype.Service;

import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.utils.session.SessionFactory;
import com.szholly.utils.session.provider.ISessionProvider;
import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.PageInfo;
import com.szholly.data.general.RowBase;
@Service
public class ExceptionService {
    
	/**
	 * 获取异常数
	 * 
	 * @param params
	 * @return
	 * @throws Exception
	 */
	public int getExceptionCount(Map<String, String> params) throws Exception {
		String sql = "select count(*) from (select * from OPERATION_DATA_ABNORMAL t"
				+ " left join (select s.name,s.wwtp_id, s.code,j.s_domaincode as f_type, j.s_domaincaption as flow_type,p.name as wwtp_name"
				+ " from BC_SITE_INFO s  left join (select s_domaincode, s_domaincaption"
				+ " from data_defulatdomain where s_domainname = 'STATION_FLOW_NAME') j on j.s_domaincode=s.flow_type"
				+ " left join sde.Ps_Wwtp p on s.wwtp_id = p.exp_no) ms on t.station_id=ms.code"
				+ " left join (select s_domaincode, s_domaincaption as exception_caption"
				+ " from data_defulatdomain where s_domainname = 'DATA_ANORMAL') k on k.s_domaincode=t.exception_code) fr where 1=1";

		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();
		if (params.containsKey("task_code")) {
			sql += " and fr.task_code = @task_code";
			listParam.add(new GlobalParameter("task_code", params.get("task_code")));
		} else {
			if (params.containsKey("wwtp_id")) {
				sql += " and fr.wwtp_id = @wwtp_id";
				listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
			}

			if (params.containsKey("flow_type")) {
				sql += " and fr.f_type = @flow_type";
				listParam.add(new GlobalParameter("flow_type", params.get("flow_type")));
			}

			if (params.containsKey("start")) {
				sql += " and fr.start_time >= to_date('" + params.get("start") + " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
			}
			if (params.containsKey("end")) {
				sql += " and fr.start_time <= to_date('" + params.get("end") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
			}
		}

		return DataUtil.getScaleInt(sql, listParam);
	}

	/**
	 * 获取异常列表
	 * 
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getExceptionList(Map<String, String> params, PageInfo pageInfo) throws Exception {

		String sql = "select * from (select * from OPERATION_DATA_ABNORMAL t"
				+ " left join (select s.name,s.wwtp_id, s.code,j.s_domaincode as f_type, j.s_domaincaption as flow_type,p.name as wwtp_name"
				+ " from BC_SITE_INFO s  left join (select s_domaincode, s_domaincaption"
				+ " from data_defulatdomain where s_domainname = 'STATION_FLOW_NAME') j on j.s_domaincode=s.flow_type"
				+ " left join sde.Ps_Wwtp p on s.wwtp_id = p.exp_no) ms on t.station_id=ms.code"
				+ " left join (select s_domaincode, s_domaincaption as exception_caption"
				+ " from data_defulatdomain where s_domainname = 'DATA_ANORMAL') k on k.s_domaincode=t.exception_code) fr where 1=1";

		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();
		if (params.containsKey("task_code")) {
			sql += " and fr.task_code = @task_code";
			listParam.add(new GlobalParameter("task_code", params.get("task_code")));
		} else {
			if (params.containsKey("wwtp_id")) {
				sql += " and fr.wwtp_id = @wwtp_id";
				listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
			}

			if (params.containsKey("flow_type")) {
				sql += " and fr.f_type = @flow_type";
				listParam.add(new GlobalParameter("flow_type", params.get("flow_type")));
			}

			if (params.containsKey("start")) {
				sql += " and fr.start_time >= to_date('" + params.get("start") + " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
			}
			if (params.containsKey("end")) {
				sql += " and fr.start_time <= to_date('" + params.get("end") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
			}
		}

		sql += " order by fr.start_time desc";

		return DataUtil.getPageRow(sql, listParam, null, pageInfo);
	}

	public List<RowBase> getAlarmInfoList(Map<String, String> params, PageInfo pageInfo) throws Exception {

		String sql = "select su.c_userealname as user_name,t.*,ms.name as station_name,pa.name as wwtp_name,d1.s_domaincaption as PARAM_NAME,d2.s_domaincaption as TYPE_NAME"
				+ " from OP_ALAR_INFO t" + " left join BC_SITE_INFO ms on t.station_code = ms.code"
				+ " left join BC_ENTERPRISE_ATTR pa on t.wwtp_id=pa.exp_no"
				+ " left join (select do.s_domaincode,do.s_domaincaption from data_defulatdomain do where do.s_domainname='MONITOR_PARAM') d1 on t.param=d1.s_domaincode"
				+ " left join (select do.s_domaincode,do.s_domaincaption from data_defulatdomain do where do.s_domainname='ALARM_INFO_TYPE') d2 on t.type=d2.s_domaincode "
				+ " left join safe_user su on t.received_by=su.s_userid where 1=1 ";

		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();
		if (params.containsKey("param")) {
			sql += " and t.param = @param";
			listParam.add(new GlobalParameter("param", params.get("param")));
		}
		if (params.containsKey("type")) {
			sql += " and t.type = @type";
			listParam.add(new GlobalParameter("type", params.get("type")));
		}
		if(params.containsKey("is_popup")){
			sql += " and t.is_popup = @is_popup";
			listParam.add(new GlobalParameter("is_popup", params.get("is_popup")));
		}
		if(params.containsKey("is_popup_not")){
			sql += " and (t.is_popup != @is_popup_not or t.is_popup is null)";
			listParam.add(new GlobalParameter("is_popup_not", params.get("is_popup_not")));
		}
		if(params.containsKey("mobile_received")){
			sql += " and t.mobile_received = @mobile_received";
			listParam.add(new GlobalParameter("mobile_received", params.get("mobile_received")));
		}
		if(params.containsKey("mobile_received_not")){
			sql += " and (t.mobile_received != @mobile_received_not or t.mobile_received is null)";
			listParam.add(new GlobalParameter("mobile_received_not", params.get("mobile_received_not")));
		}
		if (params.containsKey("wwtp_id")) {
			sql += " and t.wwtp_id = @wwtp_id";
			listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
		}
		if (params.containsKey("water_type")) {
			sql += " and ms.station_category = @water_type";
			listParam.add(new GlobalParameter("water_type", params.get("water_type")));
		}
		if (params.containsKey("flow_type")) {
			sql += " and ms.flow_type = @flow_type";
			listParam.add(new GlobalParameter("flow_type", params.get("flow_type")));
		}
		if (params.containsKey("station_id")) {
			sql += " and t.station_code = @station_id";
			listParam.add(new GlobalParameter("station_id", params.get("station_id")));
		}
		if (params.containsKey("start")) {
			sql += " and t.start_time >= to_date('" + params.get("start") + " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
		}
		if (params.containsKey("end")) {
			sql += " and t.start_time <= to_date('" + params.get("end") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
		}
		

		sql += " order by t.start_time desc";

		return DataUtil.getPageRow(sql, listParam, null, pageInfo);
	}

	public List<RowBase> getAlarmProcessList(Map<String, String> params, PageInfo pageInfo) throws Exception {

		String sql = "select su.c_userealname as user_name,d3.s_domaincode as REASON_CODE,d2.s_domaincode as ALARM_TYPE,t.type,t.param,t.station_code,t.wwtp_id, ms.name as station_name, pa.name as wwtp_name, d1.s_domaincaption as PARAM_NAME, d2.s_domaincaption as TYPE_NAME,d3.s_domaincaption as REASON_NAME,"
				+ " oap.*,ot.job_id,oj.job_name"
				+ " from OP_ALAR_PROC oap" 
				+ " left join OP_ALAR_INFO t on oap.alarm_id = t.guid"
				+ " left join BC_SITE_INFO ms on t.station_code = ms.code"
				+ " left join BC_ENTERPRISE_ATTR pa on t.wwtp_id = pa.exp_no"
				+ " left join (select do.s_domaincode, do.s_domaincaption from data_defulatdomain do  where do.s_domainname = 'MONITOR_PARAM') d1 on t.param = d1.s_domaincode"
				+ " left join (select do.s_domaincode, do.s_domaincaption from data_defulatdomain do  where do.s_domainname = '报警类型') d2 on t.type = d2.s_domaincode "
				+ " left join (select do.s_domaincode, do.s_domaincaption from data_defulatdomain do  where do.s_domainname = 'EXCEPTION_REASON') d3 on oap.exception_reason = d3.s_domaincode "
				+ " left join operation_task ot on oap.task_code = ot.task_code"
				+ " left join operation_job oj on ot.job_id = oj.guid  left join safe_user su on oap.SOLVED_BY=su.s_userid where 1=1";

		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();
		if (params.containsKey("task_code")) {
			sql += " and oap.task_code = @task_code";
			listParam.add(new GlobalParameter("task_code", params.get("task_code")));
		} else if (params.containsKey("alarm_id")) {
			sql += " and oap.alarm_id = @alarm_id";
			listParam.add(new GlobalParameter("alarm_id", params.get("alarm_id")));
		} else {
			if (params.containsKey("wwtp_id")) {
				sql += " and t.wwtp_id = @wwtp_id";
				listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
			}
			if (params.containsKey("flow_type")) {
				sql += " and ms.flow_type = @flow_type";
				listParam.add(new GlobalParameter("flow_type", params.get("flow_type")));
			}
			if (params.containsKey("water_type")) {
				sql += " and ms.station_category = @water_type";
				listParam.add(new GlobalParameter("water_type", params.get("water_type")));
			}
			if (params.containsKey("station_id")) {
				sql += " and t.station_code = @station_id";
				listParam.add(new GlobalParameter("station_id", params.get("station_id")));
			}
			if (params.containsKey("status")) {
				sql += " and oap.status in (" + params.get("status") + ")";
			}
			if (params.containsKey("start")) {
				sql += " and oap.start_time >= to_date('" + params.get("start")
						+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
			}
			if (params.containsKey("end")) {
				sql += " and oap.start_time <= to_date('" + params.get("end") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
			}
		}
		sql += " order by oap.start_time desc";

		return DataUtil.getPageRow(sql, listParam, null, pageInfo);
	}
	
	public int getAlarmProcessCount(Map<String, String> params) throws Exception {

		String sql = "select count(*) from OP_ALAR_PROC oap" 
				+ " left join OP_ALAR_INFO t on oap.alarm_id = t.guid"
				+ " left join BC_SITE_INFO ms on t.station_code = ms.code"
				+ " left join BC_ENTERPRISE_ATTR pa on t.wwtp_id = pa.exp_no"
				+ " left join (select do.s_domaincode, do.s_domaincaption from data_defulatdomain do  where do.s_domainname = 'MONITOR_PARAM') d1 on t.param = d1.s_domaincode"
				+ " left join (select do.s_domaincode, do.s_domaincaption from data_defulatdomain do  where do.s_domainname = 'ALARM_INFO_TYPE') d2 on t.type = d2.s_domaincode "
				+ " left join (select do.s_domaincode, do.s_domaincaption from data_defulatdomain do  where do.s_domainname = 'EXCEPTION_REASON') d3 on oap.exception_reason = d3.s_domaincode "
				+ " left join operation_task ot on oap.task_code = ot.task_code"
				+ " left join operation_job oj on ot.job_id = oj.guid where 1=1";

		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();
		if (params.containsKey("task_code")) {
			sql += " and oap.task_code = @task_code";
			listParam.add(new GlobalParameter("task_code", params.get("task_code")));
		} else if (params.containsKey("alarm_id")) {
			sql += " and oap.alarm_id = @alarm_id";
			listParam.add(new GlobalParameter("alarm_id", params.get("alarm_id")));
		} else {
			if (params.containsKey("wwtp_id")) {
				sql += " and t.wwtp_id = @wwtp_id";
				listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
			}
			if (params.containsKey("flow_type")) {
				sql += " and ms.flow_type = @flow_type";
				listParam.add(new GlobalParameter("flow_type", params.get("flow_type")));
			}
			if (params.containsKey("water_type")) {
				sql += " and ms.station_category = @water_type";
				listParam.add(new GlobalParameter("water_type", params.get("water_type")));
			}
			if (params.containsKey("station_id")) {
				sql += " and t.station_code = @station_id";
				listParam.add(new GlobalParameter("station_id", params.get("station_id")));
			}
			if (params.containsKey("status")) {
				sql += " and oap.status in (" + params.get("status") + ")";
			}
			if (params.containsKey("start")) {
				sql += " and oap.start_time >= to_date('" + params.get("start")
						+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
			}
			if (params.containsKey("end")) {
				sql += " and oap.start_time <= to_date('" + params.get("end") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
			}
		}
		sql += " order by oap.start_time desc";

		return DataUtil.getScaleInt(sql, listParam);
	}
	public List<RowBase> getAlarmInfoLevelList(Map<String, String> params, PageInfo pageInfo) throws Exception {
         
		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();
		
		String userId = "-1";
		try {
			ISessionProvider session = SessionFactory.getSession();
			UserEntity userEntity = (UserEntity) session.getObject(ISessionProvider.UserEntity);
			userId = userEntity.getUserID();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		String sql = "select t.*,mes.messagecontext,mes.issend,mes.c_userealname as userealname,mes.c_rolename,mes.sendtype,round(t.continue_time) as continue_time_int,"
				+ " ms.name as station_name,pa.name as wwtp_name,d1.s_domaincaption as PARAM_NAME,d2.s_domaincaption as TYPE_NAME"
				+ " from OP_ALAR_INFO t" + " left join BC_SITE_INFO ms on t.station_code = ms.code"
				+ " left join BC_ENTERPRISE_ATTR pa on t.wwtp_id=pa.exp_no"
				+ " left join (select do.s_domaincode,do.s_domaincaption from data_defulatdomain do where do.s_domainname='MONITOR_PARAM') d1 on t.param=d1.s_domaincode"
				+ " left join (select do.s_domaincode,do.s_domaincaption from data_defulatdomain do where do.s_domainname='报警类型') d2 on t.type=d2.s_domaincode ";
        
		//构造mesSql,增加时间条件
		String mesSql = "left join(select tma.alarminfoguid,wmsys.wm_concat(distinct to_char(tsr.c_rolename))c_rolename,AVG(t.issend) as issend,t.messagecontext,wmsys.wm_concat(distinct to_char(t1.c_userealname)) c_userealname,wmsys.wm_concat(distinct to_char(t.sendtype)) sendtype "
				+ " from MESSAGESEND t left join safe_user t1 on t.messagereceiver = t1.s_userid left join safe_userrole tsu on t1.s_userid = tsu.s_userid "
				+ " left join safe_role tsr on tsu.s_roleid = tsr.i_roleid "
				+ " left join messagesend_alarminfo tma on tma.messageguid = t.guid where 1=1";
		if (params.containsKey("start")) {
			mesSql += " and t.createtime >= to_date('" + params.get("start") + " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
		}
		if (params.containsKey("end")) {
			mesSql += " and t.createtime <= to_date('" + params.get("end") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
		}
		mesSql += " group by tma.alarminfoguid, t.messagecontext) mes on mes.alarminfoguid = t.guid where 1=1";
		sql += mesSql;
		
		
		if (params.containsKey("start")) {
			sql += " and t.START_TIME >= to_date('" + params.get("start") + " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
		}
		if (params.containsKey("end")) {
			sql += " and t.updatetime <= to_date('" + params.get("end") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
		}
		if (params.containsKey("param")) {
			sql += " and t.param = @param";
			listParam.add(new GlobalParameter("param", params.get("param")));
		}
		if (params.get("water_type").equals("3")) {
			sql += " and t.type in ('1','2','3','4')";
		}
		if (params.containsKey("type")) {
			sql += " and t.type = @type";
			listParam.add(new GlobalParameter("type", params.get("type")));
		}
		if(params.containsKey("is_popup")){
			sql += " and t.is_popup = @is_popup";
			listParam.add(new GlobalParameter("is_popup", params.get("is_popup")));
		}
		if(params.containsKey("is_popup_not")){
			sql += " and (t.is_popup != @is_popup_not or t.is_popup is null)";
			listParam.add(new GlobalParameter("is_popup_not", params.get("is_popup_not")));
		}
		if(params.containsKey("mobile_received")){
			sql += " and t.mobile_received = @mobile_received";
			listParam.add(new GlobalParameter("mobile_received", params.get("mobile_received")));
		}
		if(params.containsKey("mobile_received_not")){
			sql += " and (t.mobile_received != @mobile_received_not or t.mobile_received is null)";
			listParam.add(new GlobalParameter("mobile_received_not", params.get("mobile_received_not")));
		}
		if (params.containsKey("wwtp_id")) {
			sql += " and t.wwtp_id = @wwtp_id";
			listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
		}
		if (params.containsKey("water_type")) {
			sql += " and ms.station_category = @water_type";
			listParam.add(new GlobalParameter("water_type", params.get("water_type")));
		}
		if (params.containsKey("flow_type")) {
			sql += " and ms.flow_type = @flow_type";
			listParam.add(new GlobalParameter("flow_type", params.get("flow_type")));
		}
		if (params.containsKey("station_id")) {
			sql += " and t.station_code = @station_id";
			listParam.add(new GlobalParameter("station_id", params.get("station_id")));
		}
		if (params.containsKey("CONTINUE_TIME")) {
			String str=params.get("CONTINUE_TIME");
			
			if(str.equals("1")){
				sql+=" and t.CONTINUE_TIME <= "+params.get("LEVEL_ONE");
			}
			if(str.equals("2")){
				sql+=" and t.CONTINUE_TIME > "+params.get("LEVEL_ONE")+" and t.CONTINUE_TIME <= "+params.get("LEVEL_TWO");
			}
			if(str.equals("3")){
				sql+=" and t.CONTINUE_TIME > "+params.get("LEVEL_TWO");
			}
		}
		if (params.containsKey("IS_RECOVER")) {
			if (params.get("IS_RECOVER").equals("0")) {
				sql += " and t.IS_RECOVER in ('0','2')";
			}else{
				sql += " and t.IS_RECOVER in ('1')";
			}
		}
		if (params.containsKey("alarm_datasource")) {
			if (params.get("alarm_datasource").equals("redis")) {
				sql += " and (t.alarm_datasource is null or t.alarm_datasource = 'redis')";
			}else{
				sql += " and t.alarm_datasource = @alarm_datasource";
				listParam.add(new GlobalParameter("alarm_datasource", params.get("alarm_datasource")));
			}
		}
		
		if(!userId.equals("-1")){
			sql += " AND ms.code in ( select station_code from OC_ROLE_SITE_PRIVILEGE t3"+
		               "     inner join safe_userrole t4 on t4.s_userid = @userid and t3.role_id = t4.s_roleid) ";
			listParam.add(new GlobalParameter("userid", userId));
		}
		
		sql += " order by t.createtime desc ";
        
		return DataUtil.getPageRow(sql, listParam, null, pageInfo);

	}
}
